package com.excel;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import jxl.write.WriteException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.htmlparser.util.ParserException;

import com.caucho.hessian.client.HessianProxyFactory;
import com.db.connection.DBCPPostgresManager;
import com.excel.read.Config;
import com.excel.read.QueryNode;
import com.service.ImportService;

public class DB {
	public static Log log = LogFactory.getLog(DB.class);
	
	static String schemal = "adempiere";
	static List tableNames = null;
	static Map<String, String> noTableNames = new HashMap<String, String>();
//	static {
//
//		try {
//
//			Config.generateTable("c:\\table_config.xls");
//
//			FileReader fr = new FileReader(new File("c:\\noTable.txt"));
//			BufferedReader bfr = new BufferedReader(fr);
//			String str = null;
//			while ((str = bfr.readLine()) != null) {
//				if (str != null) {
//					noTableNames.put(str.toLowerCase(), "A");
//				}
//			}
//
//		} catch (FileNotFoundException e) {
//			e.printStackTrace();
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//
//	}

	public static void main(String args[]) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			conn = DBCPPostgresManager.getConnection();
			// 执行SQL语句
			 List <String>tables = getTableNames(conn);
			 for(String t: tables) {
				 getTable(conn, t);
			 }
			//init();

		} catch (Exception ex) {

			System.out.println("Error-RecordCreateService: " + ex);
		} finally {
			DBCPPostgresManager.close(conn, pstmt, rs);
		}

		
//		ExcelWriter excelWrite = new TableExcelWriter("e:\\mm.xls", getHTML(), "");
//		excelWrite.setTableDisplayName("JSON");
//		excelWrite.setSheetName("查询");
//		excelWrite.setMode(ExcelWriter.FILE_CREATE_OR_READ,
//				ExcelWriter.SHEET_CREATE_OR_READ);
//		try {
//			excelWrite.init();
//			excelWrite.writeExcel();
//		} catch (WriteException e) {
//			e.printStackTrace();
//		} catch (IOException e) {
//			e.printStackTrace();
//		} catch (SQLException e1) {
//			e1.printStackTrace();
//		}
//		
	}

	public static void init() throws WriteException, IOException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPPostgresManager.getConnection();
			List names = getTableNames(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * <br>
	 * 方法说明：获取数据元数据信息 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String table 表名称 <br>
	 * 返回类型：
	 * 
	 * @throws SQLException
	 */

	private static List getTableNames(Connection con) throws SQLException {
		List names = new ArrayList();
		StringBuffer sbf = new StringBuffer();

		DatabaseMetaData dbmd = con.getMetaData();
		String[] types = new String[1];
		ResultSet results = null;
		types[0] = "TABLE";
		results = dbmd.getTables(null, null, "%", types);

		sbf.append("<table>");
		sbf.append("<tr>");
		sbf.append("<td>代码</td><td>名称</td>");
		sbf.append("</tr>");

		while (results.next()) {
			if (results.getObject(2).equals(schemal) && !noTableNames.containsKey(results.getObject(3).toString().toLowerCase())) {
				names.add(results.getObject(3));
				sbf.append("<tr>");
				sbf.append("<td>");
				sbf.append(results.getObject(3));
				sbf.append("</td>");
				sbf.append("<td>");
				sbf.append("");
				sbf.append("</td>");
				sbf.append("</tr>");
			}
		}
		sbf.append("</table>");
		ExcelWriter excelWrite = new TableExcelWriter("e:\\m2.xls", sbf
				.toString(), "");
		excelWrite.setTableDisplayName("ADEMPIERE");
		excelWrite.setSheetName("查询");
		excelWrite.setMode(ExcelWriter.FILE_CREATE_OR_READ,
				ExcelWriter.SHEET_CREATE_OR_READ);
		try {
			excelWrite.init();
			excelWrite.writeExcel();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		return names;
	}

	private static Map<String, String> getImportKeys(Connection con,
			String table) {
		StringBuffer sbf = new StringBuffer();
		Map<String, String> aMap = new HashMap<String, String>();
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			String[] types = new String[1];
			ResultSet results = null;
			/** 表的字段信息 */
			results = dbmd.getImportedKeys(null, schemal, table);
			while (results.next()) {
				String FKTABLE_NAME = results.getString("FKTABLE_NAME");
				String FKCOLUMN_NAME = results.getString("FKCOLUMN_NAME");
				String PKTABLE_NAME = results.getString("PKTABLE_NAME");
				String PKCOLUMN_NAME = results.getString("PKCOLUMN_NAME");
				aMap.put(FKCOLUMN_NAME.toUpperCase(), PKTABLE_NAME
						.toUpperCase()
						+ "(" + PKCOLUMN_NAME.toUpperCase() + ")");
			}

		} catch (SQLException se) {
			// 输出数据库连接错误信息

			log.error("SQL Exception: " + se.getMessage());
			se.printStackTrace(System.out);
		} catch (Exception e) {
			System.out.println(e);
		} finally {

		}
		return aMap;
	}

	private static Map<String, String> getPKeys(Connection con, String table) {
		StringBuffer sbf = new StringBuffer();
		Map<String, String> aMap = new HashMap<String, String>();
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			String[] types = new String[1];
			ResultSet results = null;
			/** 表的字段信息 */
			results = dbmd.getPrimaryKeys(null, schemal, table);
			while (results.next()) {
				String PKCOLUMN_NAME = results.getString("COLUMN_NAME");
				aMap.put(PKCOLUMN_NAME.toUpperCase(), "1");
			}

		} catch (SQLException se) {
			// 输出数据库连接错误信息
			log.error("SQL Exception: " + se.getMessage());
			se.printStackTrace(System.out);
		} catch (Exception e) {
			System.out.println(e);
		} finally {

		}
		return aMap;
	}

	private static String getMeta(Connection con, String table) {

		Map<String, String> map = getImportKeys(con, table);
		Map<String, String> mapPK = getPKeys(con, table);
		StringBuffer sbf = new StringBuffer();
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			String[] types = new String[1];
			ResultSet results = null;
			/** 表的字段信息 */
			results = dbmd.getColumns(null, schemal, table, "%");
			sbf.append("<table>");
			sbf.append("<tr>");
			sbf
					.append("<td>代码</td><td>名称</td><td>数据类型</td><td>缺省值</td><td>允许为空</td><td>外键</td><td>注释</td>");
			sbf.append("</tr>");

			while (results.next()) {
				String fieldName = results.getString("COLUMN_NAME");
				int columnSize = results.getInt("COLUMN_SIZE");
				// String rec = Config.getRecord(table, "代码", fieldName, "名称");
				sbf.append("<tr>");
				sbf.append("<td>");
				sbf.append(fieldName);
				sbf.append("</td>");
				sbf.append("<td>");
				sbf.append(fieldName);
				sbf.append("</td>");
				sbf.append("<td>");
				String typeName = results.getString("TYPE_NAME");
				String realType = "varchar";
				if (typeName.equalsIgnoreCase("numeric")) {
					int floatNum = results.getInt("DECIMAL_DIGITS");
					if (floatNum > 0) {
						realType = "float";
					} else {
						realType = "bigint(" + columnSize + ")";
					}
				} else if (typeName.equalsIgnoreCase("bpchar")) {
					realType = "char(1)";
				} else if (typeName.equalsIgnoreCase("timestamp")) {
					realType = "timestamp";
				} else {
					realType = "varchar(" + columnSize + ")";
				}
				sbf.append(realType);

				sbf.append("</td>");
				sbf.append("<td>");
				String v = results.getString("COLUMN_DEF");
				sbf.append(v == null ? "" : v);
				sbf.append("<td>");
				sbf
						.append(results.getString("NULLABLE").equalsIgnoreCase(
								"0") ? "N" : "");
				sbf.append("</td>");

				sbf.append("<td>");
				if (map.get(fieldName.toUpperCase()) != null) {
					sbf.append(map.get(fieldName.toUpperCase()).toLowerCase());
				}
				// 外键
				sbf.append("</td>");

				sbf.append("<td>");
				if (mapPK.get(fieldName.toUpperCase()) != null) {
					sbf.append("PK");
				}
				sbf.append("</td>");
				sbf.append("</tr>");

			}

			sbf.append("</table>");
		} catch (SQLException se) {
			// 输出数据库连接错误信息
			log.error("SQL Exception: " + se.getMessage());
			se.printStackTrace(System.out);
		} catch (Exception e) {
			System.out.println(e);
		} finally {

		}
		return sbf.toString();
	}

	/**
	 * 根据SQL取得查询字段信息
	 * 
	 * @param con
	 * @param table
	 */
	public static void getTableFieldMeta(Connection con, String table) {

		ResultSet rs;
		Statement statement;
		String sql = "select a.stuff_name, b.name from stuff a left join location b on a.location_id = b.id";
		Vector vct = new Vector();
		try {

			// 数据库操作
			statement = con.createStatement();
			// 执行sql语句
			rs = statement.executeQuery(sql);
			if (rs.next()) {
				// 得到ResultSetMetaData
				ResultSetMetaData rsmd = rs.getMetaData();
				List<QueryNode> nodeList = new ArrayList<QueryNode>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Hashtable hst = new Hashtable();
					// 把字段名放入Name
					String name = String.valueOf(rsmd.getColumnLabel(i));
					hst.put("Name", name);
					// 把字段类型放入Type
					String type = String.valueOf(rsmd.getColumnType(i));

					String catalog = rsmd.getCatalogName(i);
					String catalogClass = rsmd.getColumnClassName(i);
					String columnName = rsmd.getColumnName(i);

					String columnType = rsmd.getColumnTypeName(i);
					String tableName = rsmd.getTableName(i);

					String schema = rsmd.getSchemaName(i);

					hst.put("Type", type);
					// 添加到Vector
					vct.add(hst);

					QueryNode queryNode = new QueryNode();
					queryNode.setTableName(tableName);
					queryNode.setColumnName(columnName);
					nodeList.add(queryNode);

				}
			}
			rs.close();
			// con.close();
		} catch (Exception ex) {
			System.out.println("Error : " + ex.toString());
		}
	}

	public void getTableMeta(Connection connection, String table)
			throws WriteException, IOException, SQLException {

		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("select  AD_MENU_ID, NAME, CREATED from "
						+ schemal + "." + table);
		String formatStr = "基本信息(ID:AD_MENU_ID,城市名:NAME);(创建时间:CREATED)";
		ExcelWriter excelWrite = new ResultSetExcelWriter("e:\\a.xls", rs,
				formatStr);
		// excelWrite.setSheetName("测试数据");
		// excelWrite.init();
		// excelWrite.writeExcel();

		rs = stmt.executeQuery("select AD_MENU_ID, NAME, CREATED from "
				+ schemal + "." + table);
		excelWrite = new ResultSetExcelWriter("e:\\b.xls", rs, formatStr);
		// excelWrite.setPosition(5, 12);
		excelWrite.setTableDisplayName("测试表格1");
		excelWrite.setSheetName("测试数据2");
		excelWrite.setMode(ExcelWriter.FILE_CREATE_OR_READ,
				ExcelWriter.SHEET_CREATE_OR_READ);
		excelWrite.init();
		excelWrite.writeExcel();

	}

	public static void getTable(Connection connection, String table)
			throws WriteException, IOException, SQLException, ParserException {

		Statement stmt = connection.createStatement();
		// 不允许删除
		// ResultSet rs = stmt
		// .executeQuery("select AD_MENU_ID, NAME, CREATED from "
		// + schemal + "." + table);
		// String formatStr = "基本信息(ID:AD_MENU_ID,城市名:NAME);(创建时间:CREATED)";
		// ExcelWriter excelWrite = new ResultSetExcelWriter("e:\\a.xls", rs,
		// formatStr);
		// // excelWrite.setSheetName("测试数据");
		// // excelWrite.init();
		// // excelWrite.writeExcel();
		//
		// rs = stmt.executeQuery("select AD_MENU_ID, NAME, CREATED from "
		// + schemal + "." + table);
		// excelWrite = new ResultSetExcelWriter("e:\\b.xls", rs, formatStr);
		// // excelWrite.setPosition(5, 12);
		// excelWrite.setTableDisplayName("测试表格1");
		// excelWrite.setSheetName("测试数据2");
		// excelWrite.setMode(ExcelWriter.FILE_CREATE_OR_READ,
		// ExcelWriter.SHEET_CREATE_OR_READ);
		// excelWrite.init();
		// excelWrite.writeExcel();
		//		
		String data = getMeta(connection, table);
		ExcelWriter excelWrite = new TableExcelWriter("e:\\mx.xls", data, "");
		excelWrite.setTableDisplayName(table);
		excelWrite.setSheetName("查询");
		excelWrite.setMode(ExcelWriter.FILE_CREATE_OR_READ,
				ExcelWriter.SHEET_CREATE_OR_READ);
		try {
			excelWrite.init();
			excelWrite.writeExcel();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

	}

	public static String getHTML() {
		return "<table id=\"table_d\"><tr>	<td>dasd</td><td>dsa</td></tr><tr>	<td>dasd</td><td>dsa</td></tr><tr>	<td>dasd</td><td>dsa</td></tr></table>";
	}

}
